/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Taxi.db;

import Taxi.model.Operation;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author U24022
 */
public class OperationsDAO extends BaseDAO {

    public int saveOperation(Operation operation) throws SQLException {
        System.out.println("INSERT");
        Statement statement;
        statement = (Statement) Database.getConnection().createStatement();
        String insertStr = "";
        insertStr = "insert into operation (unitnumber, driver, startodometer, endodometer, platenumber, start_date, end_date, boundary, taken_boundary) values("
                + quotate(operation.getUnitnumber()) + ","
                + quotate(operation.getDriver()) + ","
                + quotate(operation.getStartodometer()) + ","
                + quotate(operation.getEndodometer()) + ","
                + quotate(operation.getPlatenumber()) + ","
                + quotate(operation.getStartDate()) + ","
                + quotate(operation.getEndDate()) + ","
                + quotate(operation.getBoundary()) + ","
                + quotate(operation.getTakenBoundary())
                + ")";

        return statement.executeUpdate(insertStr);
    }

    public int updateOperation(Operation operation) throws SQLException {
        System.out.println("UPDATE");
        Statement statement;
        statement = (Statement) Database.getConnection().createStatement();
        String updateStr = "";
        updateStr = "update operation set "
                + "unitnumber =" + operation.getUnitnumber()
                + ", driver=" + quotate(operation.getDriver())
                + ", startodometer=" + quotate(operation.getStartodometer())
                + ", endodometer=" + quotate(operation.getEndodometer())
                + ", platenumber=" + quotate(operation.getPlatenumber())
                + ", start_date=" + quotate(operation.getStartDate())
                + ", end_date=" + quotate(operation.getEndDate())
                + ", boundary=" + quotate(operation.getBoundary())
                + ", taken_boundary=" + quotate(operation.getTakenBoundary())
                + " WHERE id=" + operation.getId();

        return statement.executeUpdate(updateStr);
    }

    public void deleteOperation(Operation operationFromTable) throws SQLException {

        Statement statement;
        statement = (Statement) Database.getConnection().createStatement();
        int i = statement.executeUpdate("DELETE FROM OPERATION WHERE id = '"
                + operationFromTable.getId() + "'");

    }

    public ResultSet getOperationsFromToDate(String fromDate, String toDate) throws SQLException {
        Statement statement;
        statement = (Statement) Database.getConnection().createStatement();
        String sql = "Select * from Operation where "
                + "start_date >= " + quotate(fromDate) + " AND " +
                "end_date <= " + quotate(toDate);
        System.out.println(sql);
        return (ResultSet) statement.executeQuery(sql);
    }

    public List<Operation> getOperationsFromDriverAndDate(String driver, String date) throws SQLException {
        Statement statement;
        statement = (Statement) Database.getConnection().createStatement();
        String sql = "Select * from Operation where "
                // add driver filter
                + "driver= " + quotate(driver)
                // date filter
                + (  date.isEmpty() ? "" : (" AND end_date >= " + quotate(date))   ) ;
        System.out.println(sql);
        List<Operation> operations = new ArrayList<Operation>();
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {

            Operation o = new Operation(rs.getInt("id"),
                                        rs.getString("unitnumber"),
                                        rs.getString("driver"),
                                        rs.getString("startodometer"),
                                        rs.getString("endodometer"),
                                        rs.getString("platenumber"),
                                        rs.getString("start_date"),
                                        rs.getString("end_date"),
                                        rs.getString("boundary"),
                                        rs.getString("taken_boundary"));
            operations.add(o);
        }
        return operations;
    }

    public void updateOperations(List<Operation> list) throws SQLException {
               System.out.println("UPDATE");
        Statement statement;
        
        String updateStr = "";
        for (Operation operation : list) {
            statement = (Statement) Database.getConnection().createStatement();
                   updateStr = "update operation set "
                + " end_date=" + quotate(operation.getEndDate())
                + ", boundary=" + operation.getBoundary()
                + (operation.getTakenBoundary() == null || operation.getTakenBoundary().isEmpty() ? "" : ", taken_boundary=" + operation.getTakenBoundary())
                + " WHERE id=" + operation.getId();
            System.out.println(updateStr);
            statement.executeUpdate(updateStr);
            statement.close();
        }
    }
}
